<?php 


class Trivia{
	
	public static $msgs = array();
	var $userid = '';
		
	function __construct()
	{
		$this->user_id = $_SESSION['admin_id'];
	}
	
	/* 
	***  Public Functions
	*/
	
	/*
	***	Function :This addTrivia function is use for add new menu
	*/
	 public function addTrivia($data)
	 {  //print_r($data);exit;
	    global $db, $msgError;	
		clean_array($data);	
		
		if( empty($this->msgs) )
		{
			 $record = array(
			 				 'category_id' => $data['category_id'],	
						  	 'question' => $data['question'],
							 'question_date' => toDbFormat($data['question_date']),
							 'answer_id' => $data['answer'],
							 'created_by' => $this->user_id, 
						  	 'created_on' => date('Y-m-d H:i:s')
					       );
		   
		   	 $question_id = $db->insert("quiz_question", $record);
			 
			 
			 if( count($data['option']) > 0 )
			 {
				 foreach($data['option'] as $quiz_option)
				 { 
				      $record = array('question_id' =>$question_id,'quiz_option' => $quiz_option);
				 	  $db->insert("quiz_option", $record);
				 }
			 }
			 $sql = "SELECT quiz_option_id FROM quiz_option
			 		 WHERE  question_id = '".$question_id."'
					 AND quiz_option = '".($data['answer'])."'";
			 $trivia_option_id = $db->first($sql);
			//print $db->last_query();exit;
			$quiz_option_id = $trivia_option_id['quiz_option_id'];
			 /*=== this query update answer_id in quiz_questin table*/
			 $update_record_answer = array('answer_id' => $quiz_option_id);
			 $where = array( "question_id" =>$question_id );
			 //print $db->last_query();exit;
			 $db->update("quiz_question", $update_record_answer, $where);
			
			//==========================================================
			 redirect("admin/trivia.php?added=1");		
		}
		
		$msgError = '';
		foreach($this->msgs as $msg)
		{
			$msgError .= '<p>'.$msg.'</p>';
		}

		
	 }//end addTrivia function
	//===========================
	
	
	/*
	***	Function :This updateCity function is use for update menu 
	*/	
	 public function updateTrivia($data)
	 {
	 
	    global $db, $msgError;	
		clean_array($data);	
		$question_id = intval($data['question_id']);
		if( empty($this->msgs) )
		{
		   $record = array(
							'category_id' => $data['category_id'],	
						  	 'question' => $data['question'],
							 'question_date' => toDbFormat($data['question_date']),
							 'answer_id' => $data['answer'],
							 'modified_by' => $this->user_id, 
						  	 'modified_on' => date('Y-m-d H:i:s')
					       );

			 $where = array( "question_id" =>$question_id );
			 $db->update("quiz_question", $record, $where);
			 if( count($data['option']) > 0 )
			 {
				 
				 $counter = 0;
				 foreach($data['option'] as $trivia_option)
				 { 
				      
					  $ids  = $data['option_id_exist'][$counter++]; 		
					  if($ids > 0)
					  {
						 $quiz_option_record = array('question_id' => $question_id,
							 						'quiz_option' => $trivia_option
							  						); 
						 $where_option_id = array( "quiz_option_id" =>$ids );
						 $db->update("quiz_option", $quiz_option_record, $where_option_id); 
							
					 }else{
						  $record = array('question_id' =>$question_id,'quiz_option' => $trivia_option);
					      $db->insert("quiz_option", $record);
					 }
					 					 
				 }
			   }
					$sql = "SELECT quiz_option_id FROM quiz_option
							WHERE  question_id = '".$question_id."'
							AND quiz_option = '".($data['answer'])."'"; 
					$trivia_option_id = $db->first($sql);
					$quiz_option_id = $trivia_option_id['quiz_option_id'];
					$update_record_answer = array('answer_id' => $quiz_option_id);
					$where = array( "question_id" =>$question_id );
					$db->update("quiz_question", $update_record_answer, $where);
			 //==========================================================
			 redirect("admin/trivia.php?updated=1");		
			 
		}
		
		$msgError = '';
		foreach($this->msgs as $msg)
		{
			$msgError .= '<p>'.$msg.'</p>';
		}
	 			
	}//end function updateTrivia
	
	/*
	***	Function :This getTriviasById function used for update menu
	*/
	function getTriviasByQuestionId($question_id)
	{
	 	global $db;	
		$data = $db->first("SELECT *,date_format(question_date, '%d-%m-%Y') as question_date  FROM quiz_question WHERE question_id = ".intval($question_id));
		return $data;
	}//end function getTriviasById
	//=========================
	
	function getTriviaOptionByQuestionId($question_id) 
	{
	 	global $db;	
		
		$trivia_option_data = $db->fetch_all("SELECT * FROM quiz_option WHERE question_id = '".intval($question_id)."'");
		
		foreach( $trivia_option_data as &$row ){
			
			if( $this->ifOptionExistInAnswers($row) )
			{
				$row['option_in_answer'] = 1;
			}
			else
			{
				$row['option_in_answer'] = 0;
			}
		}
		
		
		return $trivia_option_data;
	}//end function getTriviasById
	
	function getQuizOptionAnswer($question_id)
	{
	    global $db;	
		$sql = "SELECT qq.answer_id,qo.quiz_option
				FROM quiz_question qq 
				INNER JOIN quiz_option qo
				ON qo.quiz_option_id = qq.answer_id
				AND qq.question_id = '".intval($question_id)."'";
		$result = $db->first($sql);
	    return $result;
	}
	/*
	***	Function :This deleteTrivia function used for update menu
	*/
	function deleteTrivia($question_id)
	{
		global $db, $msgError;
		
		$question_id = intval($question_id);
		
		$query = $db->query("select * from quiz_answer where question_id = '".$question_id."'");
		if( $db->num_rows($query) > 0 )
		{
			$this->msgs[] = 'This trivia question  is associated with trivia answer, Can not delete this trivia question.';	
		}
		
		if( empty($this->msgs) )
		{
			$where = array( 'question_id' => $question_id );
			if( $db->delete("quiz_question", $where) )
			{ 
			   $question_ref_id = intval($question_id);
			   $where_option = array('question_id'=> $question_ref_id );
			   $db->delete("quiz_option", $where_option);	
			   redirect("admin/trivia.php?deleted=1");	
			}
			else
			{
				$this->msgs[] = 'Some error occurs. Trivia question can not be deleted.';
			}
		}
	
		$msgError = '';
		foreach($this->msgs as $msg)
		{
			$msgError .= '<p>'.$msg.'</p>';
		}
		
	}
	
	/*
	***	Function :This getCities function is use for listing all menus on mange menus listing page 
	*/
	
	function getTrivias($data)
	{
		global $db, $pager, $config;
		$pager = new Paginator();
		//print_r($data);exit;
		if( isset($data['question']) && $data['question'] != '')
		{
			$where = " WHERE question like '%" . $db->escape($data['question']) . "%'";
		
		}
		
		if( isset($data['new_page_limit']) && intval($data['new_page_limit']) > 0 ){
			$config['page_limit'] = $data['new_page_limit'];
		}
		$page_limit = $config['page_limit']; 
		if(isset($_GET['pg'])&& $_GET['pg'] > 0 ){ 
			$limit_lenght  = (($_GET['pg']-1)*$page_limit) ; 
			
			$limit = "limit $limit_lenght, $page_limit ";
		}else{
			$limit = "limit 0 , $page_limit ";
			if($data['new_page_limit'] == 'all') 
				$limit = '';
		}
		

		$sql = "SELECT SQL_CALC_FOUND_ROWS * FROM quiz_question $where order by question_id $limit";
        $result = $db->fetch_all($sql);
 		$counter = $db->queryCount();
		$pager->items_total = $counter[0];
		$pager->default_ipp = ($data['new_page_limit'] == 'all') ? $pager->items_total : $config['page_limit'];
		$pager->paginate($path);
		return $result;
		
	
	}//end getTrivias function
	//===========================
	
	
	public function isTriviaExist($Trivia_question, $Trivia_id = '')
	{
		global $db;
		$where = '';
		if($Trivia_id != '')
		{
			$where = ' AND Trivia_id != '.intval($Trivia_id);
		}
	   $sql = "select * from pcb_Trivia_question  where Trivia_question = '".$db->escape($Trivia_question)."' $where ";
	   
		$query = $db->query($sql); 
		if( $db->num_rows($query) > 0 )
		{	
			return TRUE;
		}	
		else
		{
			return FALSE;	
		}
	}
	//================================
	/***
	*	Function : this function changeTriviaStatus used for change the status of menu
	*/
	
	function changeTriviaStatus($Trivia_id, $Trivia_status)
	{        
		global $db;	
		$record = array("active" => intval($Trivia_status) );
		$where = array( "Trivia_id" => intval($Trivia_id) );	
		if( $db->update("pcb_Trivia_question", $record, $where) )
		{
			redirect("admin/Trivia.php?updated=1");
		}
	}
	
	function ifOptionExistInAnswers($data)
	{
		global $db;
		$sql= "select answer_id from quiz_answer where answer_id = '".$data['quiz_option_id']."'";
	    $query = $db->query($sql);
		if( $db->num_rows($query) > 0 )
		 {
		   	return 1;
		 }
		 else
		 {
			return 0;	 
		 }
	}
	
    function removeTriviaOptions($data)
	{      global $db;
		  
		$sql= "select answer_id from quiz_answer where answer_id = '".$data['quiz_option_id']."'";
	    $query = $db->query($sql);
		if( $db->num_rows($query) > 0 )
		  {
		    	return 1;
		  }else{
		   	    
		       $where = array('quiz_option_id' => $data['quiz_option_id']);
		       return $db->delete("quiz_option", $where);
		  }
		  // print $db->last_query();exit;
	}	
	function deleteOptionByQuestionId($question_id)
	{    
	        global $db;	 
	        $where = array('question_id' => $data['question_id']);
		    $db->delete("quiz_option", $where);
	}	
	function TriviaFeatured($Trivia_id)
	{        
		global $db;	
		$record_set_other = array("featured" =>0 );
		$db->update("pcb_Trivia_question", $record_set_other);
		$record = array("featured" =>1 );
		$where = array( "Trivia_id" => intval($Trivia_id));	
		$db->update("pcb_Trivia_question", $record, $where);
		redirect("admin/Trivia.php?updated=1");
		
	}
	
	function getTriviaResult($data)
	{
		global $db, $pager, $config;
		$pager = new Paginator();
		
		$config['page_limit'] = 25;
		
		if( isset($data['new_page_limit']) && intval($data['new_page_limit']) > 0 ){
			$config['page_limit'] = $data['new_page_limit'];
		}
		$page_limit = $config['page_limit']; 
		if(isset($_GET['pg'])&& $_GET['pg'] > 0 ){ 
			$limit_lenght  = (($_GET['pg']-1)*$page_limit) ; 
			
			$limit = "limit $limit_lenght, $page_limit ";
		}else{
			$limit = "limit 0 , $page_limit ";
			if($data['new_page_limit'] == 'all') 
				$limit = '';
		}
		
		$sql = "select SQL_CALC_FOUND_ROWS ppq.user_id, ppq.name, ppq.email, ppq.contact, ppq.city from pcb_player_question ppq where ppq.user_type = 'trivia' $limit";
		$users = $db->fetch_all($sql);
				
		$users = $db->fetch_all($sql);
 		$counter = $db->queryCount();
		$pager->items_total = $counter[0];
		$pager->default_ipp = ($data['new_page_limit'] == 'all') ? $pager->items_total : $config['page_limit'];
		$pager->paginate($path);

		foreach($users as &$row)
		{
			$sql = " select * from quiz_answer where user_id = ".intval($row['user_id'])." ";
			$questions = $db->fetch_all($sql);
			$total_question = count($questions);

			$correct_answers = 0;
			
			foreach($questions as $row2)
			{
				$sql = "select * from quiz_question where question_id = '".$row2['question_id']."' and  answer_id = '".$row2['answer_id']."'";
				$query = $db->query($sql); 
				if( $db->num_rows($query) > 0 )	
				{	
					$correct_answers++;
				}
			} 

			$result = array('total'=> $total_question, 'correct' => $correct_answers);
			
			$row['total_questions'] = $total_question;
			$row['total_correct'] = $correct_answers;
			$row['total_wrong'] = $total_question - $correct_answers;
			
			//echo "<pre> $row[name] | $row[email] | $row[total_questions] |  $row[total_correct] | $row[total_wrong]  </pre><br>";
			
			
		}
	
		return $users;
	
	}
	
	
	function changeStatus($data)
	{        
		global $db;	
		
		$status = $data['status'];
		$question_id = $data['question_id'];
		
		$record = array("active" => intval($status) );
		$where = array( "question_id" => intval($question_id) );	
		if( $db->update("quiz_question", $record, $where) )
		{
			if(intval($status) == 1 )
				$status = 1;
			else
				$status = 0;
			redirect("admin/trivia.php?updated=".intval($status));
		}
	}
	
	function getCategoriesDropDown($data)
	{
		global $db;
		$sql = "SELECT * FROM category order by category_name";
		$categories = $db->fetch_all($sql);
		
		$html = '<select name="category_id" id="category_id" >
			            <option value="" >--select--</option>';
		foreach($categories as $category)
		{
			$selected = ( $category['category_id'] == $data['category_id'] ) ? "selected" : '';
			$html .= "<option {$selected} value=\"{$category[category_id]}\">{$category[category_name]}</option>";
		}		 
		 $html .= '</select>';
	   return $html ;
	}
	
	
	
}//end class
?>